package com.cn.hu.service.impl;

import java.io.IOException;
import java.io.InputStream;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.apache.poi.hssf.usermodel.HSSFDateUtil;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.stereotype.Service;

import com.cn.hu.entity.ContractDAO;
import com.cn.hu.entity.Customer;
import com.cn.hu.entity.CustomerContacts;
import com.cn.hu.entity.CustomerDAO;
import com.cn.hu.entity.CustomerDepartment;
import com.cn.hu.entity.CustomerDepartmentDAO;
import com.cn.hu.entity.Department;
import com.cn.hu.entity.DepartmentDAO;
import com.cn.hu.entity.Employee;
import com.cn.hu.entity.EmployeeDAO;
import com.cn.hu.entity.Plan;
import com.cn.hu.entity.PlanDAO;
import com.cn.hu.entity.SpringDAO;
import com.cn.hu.entity.User;
import com.cn.hu.entity.UserDAO;
import com.cn.hu.entity.UserRule;
import com.cn.hu.entity.UserRuleDAO;
import com.cn.hu.entity.UserRuleMenu;
import com.cn.hu.entity.UserRuleMenuDAO;
import com.cn.hu.entity.vo.Result;
import com.cn.hu.entity.vo.SelectResult;
import com.cn.hu.service.WebService;

@Service("webService")
public class WebServiceImpl implements WebService {

	@Autowired
	UserDAO userDao;

	@Autowired
	EmployeeDAO employeeDao;

	@Autowired
	DepartmentDAO departmentDao;

	@Autowired
	SpringDAO springDao;

	@Autowired
	CustomerDAO customerDao;

	@Autowired
	PlanDAO planDao;

	@Autowired
	ContractDAO contractDao;
	
	@Autowired
	CustomerDepartmentDAO customerDepartmentDao;
	
	@Autowired
	UserRuleMenuDAO menuDao;
	
	@Autowired
	UserRuleDAO ruleDao;

	static SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");


	@Override
	public User findUserByAccount(String username) {
		List<User> userList = userDao.findByAccount(username);
		if(!userList.isEmpty()){
			User user =userList.get(0);
			//查询用户对象的角色
			UserRule rule = ruleDao.findById(user.getRuleId());  
			//查询角色对应的菜单集合
			String sql = "select u.* from user_rule_menu u left join rule_menu_ref rmr on u.id = rmr.menu_id where rmr.rule_id = :ruleId";
			Map<String,Object> paramMap = new HashMap<>();
			paramMap.put("ruleId", rule.getId());
			List<UserRuleMenu> menuList =springDao.getNamedJdbcTemplate().query(sql, paramMap, new RowMapper<UserRuleMenu>(){
				UserRuleMenu menu;
				@Override
				public UserRuleMenu mapRow(ResultSet rs, int num)
						throws SQLException {
					menu = new UserRuleMenu();
					menu.setBak(rs.getString("bak"));
					menu.setId(rs.getInt("id"));
					menu.setMenuCode(rs.getString("menu_code"));
					menu.setMenuLink(rs.getString("menu_link"));
					menu.setIcon(rs.getString("icon"));
					menu.setMenuName(rs.getString("menu_name"));
					return menu;
				}});
			user.setMenuList(menuList);
			user.setRule(rule);
			return user;
		}
		return null;
	}

	@Override
	public boolean saveOrUpdateUser(User u) {
		boolean res =false;
		try {
			userDao.attachDirty(u);
			res=true;
		} catch (Exception e) {
			e.printStackTrace();
		}
		return res;
	}

	@Override
	public Result<Employee, String> loadEmployeeList(
			Result<Employee, String> res,Employee employee) {
		Map<String, Object> paramMap = new HashMap<String, Object>();
		StringBuilder sql= new StringBuilder("select count(*) from employee e where e.status <> -1");
		StringBuilder listSql =new StringBuilder("select e.*,d.department_name from employee e left join department d on e.department_id=d.id where e.status <> -1 ");
		if(employee!=null){
			if(employee.getName()!=null){
				sql.append(" and e.name like '%"+employee.getName()+"%' ");
				listSql.append(" and e.name like '%"+employee.getName()+"%' ");
			}
			if(employee.getSex()!=null){
				if(employee.getSex()!=-1){
					sql.append(" and e.sex=:sex ");
					listSql.append(" and e.sex=:sex ");
					paramMap.put("sex", employee.getSex());
				}
			}
			if(employee.getMobile()!=null){
				sql.append(" and e.mobile like '%"+employee.getMobile()+"%' ");
				listSql.append(" and e.mobile like '%"+employee.getMobile()+"%' ");
			}
			if(employee.getStatus()!=null){
				if(employee.getStatus()!=-1){
					sql.append(" and e.status=:status ");
					listSql.append(" and e.status=:status ");
					paramMap.put("status", employee.getStatus());
				}
			}
		}
		int total = springDao.getNamedJdbcTemplate().queryForInt(sql.toString(), paramMap);
		listSql.append("  order by e.id desc limit :start,:number ");

		paramMap.put("start", res.getiDisplayStart());
		paramMap.put("number", res.getiDisplayLength());
		List<Employee> list = springDao.getNamedJdbcTemplate().query(listSql.toString(), paramMap, new RowMapper<Employee>() {
			Employee e;
			@Override
			public Employee mapRow(ResultSet rs, int num) throws SQLException {
				e=new Employee();
				e.setAge(rs.getInt("age"));
				e.setBak(rs.getString("bak"));
				e.setCreateDate(rs.getTimestamp("create_date"));
				e.setDepartmentName(rs.getString("department_name"));
				e.setDepartmentId(rs.getInt("department_id"));
				e.setEmail(rs.getString("email"));
				e.setId(rs.getInt("id"));
				e.setMobile(rs.getString("mobile"));
				e.setName(rs.getString("name"));
				e.setSex(rs.getInt("sex"));
				e.setStatus(rs.getInt("status"));
				e.setTel(rs.getString("tel"));
				return e;
			}
		});
		res.setSuccess(true);
		res.setErrorCode(0);
		res.setAaData(list);
		res.setiTotalDisplayRecords(total);
		res.setiTotalRecords(total);
		return res;
	}

	@SuppressWarnings("unchecked")
	@Override
	public List<Department> findAllDepartment() {
		return departmentDao.findAll();
	}

	@Override
	public boolean saveOrUpdateEmployee(Employee e) {
		boolean success=false;
		try {
			employeeDao.attachDirty(e);
			success=true;
		} catch (Exception e2) {
			e2.printStackTrace();
		}
		return success;
	}

	@Override
	public boolean deleteEmployeeById(Integer delId) {
		boolean success=false;
		try {
			Employee emp=employeeDao.findById(delId);
			emp.setStatus(-1);
			success=true;
		} catch (Exception e2) {
			e2.printStackTrace();
		}
		return success;
	}

	@Override
	public Result<Customer, String> loadCustomerList(
			Result<Customer, String> res, Customer customer) {
		Map<String, Object> paramMap=new HashMap<>(); 
		StringBuilder countSql= new StringBuilder("select count(*) from customer c where c.status=1 ");
		StringBuilder listSql=new StringBuilder("select c.* from customer c where c.status=1 ");
		if(customer!=null){
			if(customer.getName()!=null){
				countSql.append(" and c.name like '%"+customer.getName()+"%' ");
				listSql.append(" and c.name like '%"+customer.getName()+"%' ");
			}
			if(customer.getTel()!=null){
				countSql.append(" and c.tel like '%"+customer.getTel()+"%' ");
				listSql.append(" and c.tel like '%"+customer.getTel()+"%' ");
			}
			if(customer.getMobile()!=null){
				countSql.append(" and c.mobile like '%"+customer.getMobile()+"%' ");
				listSql.append(" and c.mobile like '%"+customer.getMobile()+"%' ");
			}
			if(customer.getAddress()!=null){
				countSql.append(" and c.address like '%"+customer.getAddress()+"%' ");
				listSql.append(" and c.address like '%"+customer.getAddress()+"%' ");
			}
			if(customer.getEmail()!=null){
				countSql.append(" and c.email like '%"+customer.getEmail()+"%' ");
				listSql.append(" and c.email like '%"+customer.getEmail()+"%' ");
			}
		}

		listSql.append(" order by c.id desc limit :start,:number ");
		int total = springDao.getNamedJdbcTemplate().queryForInt(countSql.toString(), paramMap);
		paramMap.put("start", res.getiDisplayStart());
		paramMap.put("number", res.getiDisplayLength());

		List<Customer> list= springDao.getNamedJdbcTemplate().query(listSql.toString(), paramMap, new RowMapper<Customer>() {
			Customer c;
			@Override
			public Customer mapRow(ResultSet rs, int num)
					throws SQLException {
				c=new Customer();
				c.setAddress(rs.getString("address"));
				c.setBak(rs.getString("bak"));
				c.setCreateDate(rs.getTimestamp("create_date"));
				
				c.setEmail(rs.getString("email"));
				c.setId(rs.getInt("id"));
				c.setMobile(rs.getString("mobile"));
				c.setModifyDate(rs.getTimestamp("modify_date"));
				c.setName(rs.getString("name"));
				c.setStatus(rs.getInt("status"));
				c.setTel(rs.getString("tel"));
				return c;
			}
		});
		res.setSuccess(true);
		res.setAaData(list);
		res.setiTotalDisplayRecords(total);
		res.setiTotalRecords(total);
		return res;
	}

	@Override
	public boolean saveOrUpdateCustomer(Customer customer) {
		boolean res =false;
		try {
			customer.setStatus(1);
			if(customer.getId()!=null){
				customer.setModifyDate(new Timestamp(System.currentTimeMillis()));
			}
			customerDao.attachDirty(customer);
			res=true;
		} catch (Exception e) {
			e.printStackTrace();
		}
		return res;
	}

	@Override
	public boolean deleteCustomer(int delId) {
		boolean res =false;
		try {
			Customer customer =customerDao.findById(delId);
			if(customer!=null){
				customer.setStatus(0);
			}
			res=true;
		} catch (Exception e) {
			e.printStackTrace();
		}
		return res;
	}

	@Override
	public Result<CustomerContacts, String> loadCustomerContactsList(
			Result<CustomerContacts, String> res, CustomerContacts contacts,Integer customerId) {
		Map<String, Object> map = new HashMap<>();
		StringBuilder countSql= new StringBuilder("select count(*) from customer_contacts cc where cc.customer_id=:customerId ");
		StringBuilder listSql=new StringBuilder("select cc.* from customer_contacts cc where cc.customer_id=:customerId ");

		listSql.append(" order by cc.id desc limit :start,:number ");
		map.put("customerId", customerId);
		int total=springDao.getNamedJdbcTemplate().queryForInt(countSql.toString(), map);
		map.put("start", res.getiDisplayStart());
		map.put("number", res.getiDisplayLength());

		List<CustomerContacts> list =springDao.getNamedJdbcTemplate().query(listSql.toString(), map, new RowMapper<CustomerContacts>() {
			CustomerContacts c;
			@Override
			public CustomerContacts mapRow(ResultSet rs, int num)
					throws SQLException {
				c=new CustomerContacts();
				c.setAge(rs.getInt("age"));
				c.setBak(rs.getString("bak"));
				c.setCreateDate(rs.getTimestamp("create_date"));
				c.setEmail(rs.getString("email"));
				c.setId(rs.getInt("id"));
				c.setMobile(rs.getString("mobile"));
				c.setModifyDate(rs.getTimestamp("modify_date"));
				c.setName(rs.getString("name"));
				c.setSex(rs.getInt("sex"));
				c.setStatus(rs.getInt("status"));
				c.setTel(rs.getString("tel"));
				return c;
			}
		});


		res.setSuccess(true);
		res.setiTotalDisplayRecords(total);
		res.setiTotalRecords(total);
		res.setAaData(list);
		return res;
	}

	@Override
	public Result<Plan, String> loadExcelToList(InputStream stream,int excelType) {
		Result<Plan, String> res = new Result<>();

		Workbook hwb;
		Row row = null;
		Cell cell;
		Plan plan;
		double num;
		Sheet sheet;
		List<Plan> planList = new ArrayList<>();
		try {
			if (excelType==0) {
				hwb= new HSSFWorkbook(stream);
			}else{
				hwb= new XSSFWorkbook(stream);
			}
			//得到第一个工作表
			//for(int s=0;s<hwb.getNumberOfSheets();s++){
			sheet = hwb.getSheetAt(0);
			//遍历该表格中所有的工作表，i表示工作表的数量 getNumberOfSheets表示工作表的总数
			for(int i = 0; i < hwb.getNumberOfSheets(); i++) {   
				sheet = hwb.getSheetAt(i);
				//遍历该表所有的行,不包括第一行标题行，j表示行数 getPhysicalNumberOfRows行的总数   
				for(int j = 1; j < sheet.getPhysicalNumberOfRows(); j++) {   
					row = sheet.getRow(j);
					plan=new Plan();
					plan.setId(j);
					//遍历该行所有的列
					for(int c=0;c<row.getPhysicalNumberOfCells();c++){
						cell = row.getCell(c);
						switch(c){
						case 0://订单时间
							plan.setOrderDate(convertValue(cell));
							break;
						case 1://商品名称
							plan.setGoodsName(convertValue(cell));
							break;
						case 2://商品规格
							plan.setGoodsType(convertValue(cell));
							break;
						case 3://商品单位
							plan.setGoodsUnit(convertValue(cell));
							break;
						case 4://商品数量
							num=(Double.parseDouble(convertValue(cell)));
							plan.setGoodsNum(num);
							break;
						case 5://采购单位
							plan.setPlanUnit(convertValue(cell));
							break;
						case 6://所在部门
							plan.setDeparmentName(convertValue(cell));
							break;
						case 7://使用人
							plan.setContactsName(convertValue(cell));
							break;
						case 8://操作人
							plan.setEmployeeName(convertValue(cell));
							break;
						}
					}
					planList.add(plan);
				}
			}
			//}
			res.setSuccess(true);
			res.setAaData(planList);
			int total =planList.size();
			res.setiTotalDisplayRecords(total);
			res.setiTotalRecords(total);
			res.setiDisplayLength(total);
			res.setMessage("文件读取成功！请预览无误后进行保存操作");
		}	catch (NumberFormatException e) {
			e.printStackTrace();
			res.setSuccess(false);
			res.setMessage("您的文件中有数字不符合规范的情况");
		} catch (IOException e) {
			e.printStackTrace();
			res.setSuccess(false);
			res.setMessage("解析excel错误");
		}
		return res;
	}

	/**
	 * 转换cell中的值
	 * @param cell
	 * @return
	 */
	public static String convertValue(Cell cell){
		String value="";
		if(cell==null){
			return "NULL";
		}
		int rowType=cell.getCellType();
		if( rowType== Cell.CELL_TYPE_STRING){
			value=cell.getStringCellValue();      
		}else if(rowType == Cell.CELL_TYPE_NUMERIC){
			if(HSSFDateUtil.isCellDateFormatted(cell)){
				value=sdf.format(HSSFDateUtil.getJavaDate(cell.getNumericCellValue())).toString();
			}else{
				Number v=cell.getNumericCellValue();
				value=v.toString();;
			}

		}
		return value;
	}

	@SuppressWarnings("unchecked")
	@Override
	public List<Employee> findEmployeeList() {
		return employeeDao.findAll();
	}

	@SuppressWarnings("unchecked")
	@Override
	public List<Customer> findAllCustomerList() {
		return customerDao.findAll();
	}

	@Override
	public List<CustomerDepartment> findDeparmentListByCustomerId(
			Integer customerId) {
		List<CustomerDepartment> res = new ArrayList<>();
		Map<String, Object> paramMap = new HashMap<>();
		paramMap.put("customerId", customerId);
		try {
			String sql ="select c.id,c.department_name from customer_department c where c.customer_id = :customerId ";
			//res = customerDepartmentDao.getCurrentSession().createSQLQuery(sql).setInteger("customerId", customerId).setEntity("c", CustomerDepartment.class).list();
			res = springDao.getNamedJdbcTemplate().query(sql, paramMap, new RowMapper<CustomerDepartment>() {

				@Override
				public CustomerDepartment mapRow(ResultSet rs, int num)
						throws SQLException {
					CustomerDepartment c = new CustomerDepartment(rs.getInt("id"),rs.getString("department_name"));
					return c;
				}
			});
		} catch (Exception e) {
			e.printStackTrace();
		}
		return res;
	}

	@Override
	public List<SelectResult> findContactsListByDepId(Integer selectId) {
		List<SelectResult> list = new ArrayList<>();
		Map<String, Object> paramMap = new HashMap<>();
		try {
			String sql = "select c.id,c.name from customer_contacts c where c.department_id =:selectId";
			paramMap.put("selectId", selectId);
			list = springDao.getNamedJdbcTemplate().query(sql, paramMap, new RowMapper<SelectResult>() {

				@Override
				public SelectResult mapRow(ResultSet rs, int num)
						throws SQLException {
					SelectResult r = new SelectResult();
					r.setId(rs.getInt("id"));
					r.setText(rs.getString("name"));
					return r;
				}
			});
		} catch (Exception e) {
			e.printStackTrace();
		}
		return list;
	}

	@Override
	public Result<String, String> checkAddPlanList(List<Plan> planList) {
		Result<String, String> res = new Result<>();
		int line = 1;
		res.setSuccess(true);
		/*StringBuilder oneHql =  null;
		Customer customer = null;
		StringBuilder twoHql = null;*/
		for(Plan plan:planList){
			line++;
			/*customer = null;
			oneHql =  new StringBuilder("select count(c.id) from customer c  ");
			twoHql = new  StringBuilder("  ");
			//判断客户单位是否存在，
			if(plan.getCustomerId()!=null)
			{
				oneHql.append(" where c.id = ?");
				customer = customerDao.findById(plan.getCustomerId());
			}
			else
			{
				oneHql.append(" where c.name = ? ");
				List<Customer> list =customerDao.findByName(plan.getPlanUnit());
				if(list!=null&&list.size()>0)
				{
					customer = list.get(0);
				}
			}
			if(customer ==null)
			{
				res.setSuccess(false);
				res.setMessage("第"+line+"行客户不存在，请检查");
				break;
			}*/
			if(!isRightData(plan)){
				res.setMessage("第"+line+"行客户或部门或联系人有误，请检查后再执行导入操作");
				res.setSuccess(false);
				break;
			}
			
		}
		return res;
	}

	@Override
	public Result<String, String> savePlanList(List<Plan> planList) {
		Result<String, String> res = new Result<>();
		Timestamp ts = new Timestamp(System.currentTimeMillis());
		try {
			for(Plan p:planList){
				p.setCreateDate(ts);
				p.setStatus(1);
			}
			res.setSuccess(true);
			res.setMessage("导入数据成功");
		} catch (Exception e) {
			e.printStackTrace();
			res.setSuccess(false);
			res.setMessage(e.getMessage());
		}
		return res;
	}
	
	public   boolean  isRightData(Plan plan){
		boolean exist = false;
		StringBuilder sql = new StringBuilder("select count(*) from customer c left join customer_department cd on c.id = cd.customer_id left join customer_contacts cc on cd.id = cc.department_id  ");
		Map<String,Object> map = new HashMap<>();
		if(plan.getCustomerId()!=null){
			sql.append(" where c.id = :aid ");
			map.put("aid", plan.getCustomerId());
		}else{
			sql.append(" where c.name =:aname ");
			map.put("aname", plan.getPlanUnit());
		}
		if(plan.getDepId()!=null){
			sql.append(" and cd.id = :bid ");
			map.put("bid", plan.getDepId());
		}else{
			sql.append(" and cd.department_name=:bname ");
			map.put("bname", plan.getDeparmentName());
		}
		if(plan.getContactsId()!=null)
		{
			sql.append(" and cc.id = :cid ");
			map.put("cid", plan.getContactsId());
		}else{
			sql.append(" and cc.name = :cname ");
			map.put("cname", plan.getContactsName());
		}
		int num =springDao.getNamedJdbcTemplate().queryForInt(sql.toString(),map );
		if(num>0){
			exist = true;
		}
		return exist;
	}

}
